package com.wxzhou.wangzhuan.repository;

import com.wxzhou.wangzhuan.dto.BindRecordRequestDto;
import com.wxzhou.wangzhuan.dto.BindRecordResponseDto;
import com.wxzhou.wangzhuan.dto.WithdrawRecordRequestDto;
import com.wxzhou.wangzhuan.dto.WithdrawRecordResponseDto;
import com.wxzhou.wangzhuan.entity.SysUser;
import com.wxzhou.wangzhuan.entity.UserBindRelation;
import com.wxzhou.wangzhuan.entity.WzUser;
import io.smallrye.mutiny.Uni;
import io.vertx.mutiny.sqlclient.Pool;
import io.vertx.mutiny.sqlclient.Row;
import io.vertx.mutiny.sqlclient.Tuple;
import jakarta.enterprise.context.ApplicationScoped;
import jakarta.inject.Inject;

import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * 用户认证数据访问层
 */
@ApplicationScoped
public class SysUserRepository {

    @Inject
    Pool client;

    /**
     * 根据设备ID和渠道查找用户
     */
    public Uni<SysUser> findByDeviceId(String deviceId,String channel) {
        String sql = "SELECT * FROM wz_users WHERE device_id = $1 AND channel = $2 LIMIT 1";
        return client.preparedQuery(sql)
                .execute(Tuple.of(deviceId,channel))
                .onItem().transform(rows -> {
                    if (rows.size() > 0) {
                        return mapRowToSysUser(rows.iterator().next());
                    }
                    return null;
                });
    }

    /**
     * 根据用户ID查找用户
     */
    public Uni<SysUser> findByUserId(Long userId) {
        String sql = "SELECT * FROM wz_users WHERE id = $1";
        return client.preparedQuery(sql)
                .execute(Tuple.of(userId))
                .onItem().transform(rows -> {
                    if (rows.size() > 0) {
                        return mapRowToSysUser(rows.iterator().next());
                    }
                    return null;
                });
    }

    /**
     * 根据用户ID查找用户
     */
    public Uni<WzUser> findById(Long userId) {
        String sql = "SELECT * FROM wz_users WHERE id = $1";
        return client.preparedQuery(sql)
                .execute(Tuple.of(userId))
                .onItem().transform(rows -> {
                    if (rows.size() > 0) {
                        return mapRowToWzUser(rows.iterator().next());
                    }
                    return null;
                });
    }

    /**
     * 查询主账号用户绑定关系
     */
    public Uni<List<UserBindRelation>> findAllBindRecordById(Long userId) {
        String sql = "SELECT * FROM wz_jxb_user_bind_relation WHERE main_user_id = $1";
        return client.preparedQuery(sql)
                .execute(Tuple.of(userId))
                .onItem().transform(rows -> {
                    List<UserBindRelation> configs = new ArrayList<>();
                    for (Row row : rows) {
                        configs.add(mapRowToUserBindRelation(row));
                    }
                    return configs;
                });
    }

    /**
     * 绑定子账号和主账号的关系
     */
    public Uni<Integer> bindSubUserFromMainUser(Long subUserId,Long mainUserId) {
        return client.withTransaction(conn -> {
            // 修改后的INSERT语句，使用子查询验证用户ID存在性
            String sql = "INSERT INTO wz_jxb_user_bind_relation(user_id, created_at, main_user_id) " +
                    "SELECT $1, $2, $3 " +
                    "WHERE EXISTS (SELECT 1 FROM wz_users WHERE id = $1) " +
                    "AND EXISTS (SELECT 1 FROM wz_users WHERE id = $3) " +
                    "RETURNING *";

            return conn.preparedQuery(sql)
                    .execute(Tuple.of(subUserId,LocalDateTime.now(), mainUserId))
                    .onItem().transformToUni(rows -> {
                        if (rows.size() > 0) {
                            return conn.preparedQuery("UPDATE wz_users SET pid = $1 WHERE id = $2").execute(Tuple.of(mainUserId, subUserId)).onItem().transform(rows1 -> rows1.size());
                        }
                        return Uni.createFrom().item(0);
                    });
        });
    }


    public Uni<List<BindRecordResponseDto>> findWithPagination(BindRecordRequestDto pageRequest) {
        QueryConditionUtil queryCoinlogStrBy = createQueryStrBy(pageRequest);
        StringBuilder sql = queryCoinlogStrBy.getPageStr();
        List<Object> pageParams = queryCoinlogStrBy.getPageParams();

        return client.preparedQuery(sql.toString())
                .execute(Tuple.from(pageParams))
                .onItem().transform(rows -> {
                    List<BindRecordResponseDto> configs = new ArrayList<>();
                    for (Row row : rows) {
                        configs.add(mapRowToBindRelation(row));
                    }
                    return configs;
                });
    }

    public Uni<Long> countOf(BindRecordRequestDto pageRequest) {
        QueryConditionUtil queryCoinlogStrBy = createQueryStrBy(pageRequest);
        List<Object> paramList = queryCoinlogStrBy.getParams();

        Tuple params = paramList.isEmpty() ? Tuple.tuple() : Tuple.from(paramList);

        return client.preparedQuery(queryCoinlogStrBy.getQueryStr().toString())
                .execute(params)
                .onItem().transform(rows -> {
                    if (rows.size() > 0) {
                        return rows.iterator().next().getLong("count");
                    }
                    return 0L;
                });
    }
    private QueryConditionUtil createQueryStrBy(BindRecordRequestDto pageRequest) {

        QueryConditionUtil queryConditionUtil = new QueryConditionUtil();
        StringBuilder sql = new StringBuilder("SELECT DISTINCT a.* FROM wz_jxb_user_bind_relation a WHERE a.main_user_id is not null ");

        List<Object> params = new ArrayList<>();

        int size = pageRequest.getLimit();
        int offset = pageRequest.getOffset();
        if (pageRequest.getUserid() != null && pageRequest.getUserid() > 0L) {
            sql.append(" AND a.main_user_id = $").append(params.size() + 1);
            params.add(pageRequest.getUserid());
        }

        queryConditionUtil.setQueryStr(sql);
        queryConditionUtil.setParams(params);
        List<Object> pageParams = new ArrayList<>();
        pageParams.addAll(params);

        StringBuilder pageSql = new StringBuilder(sql.toString());
        // 分页
        pageSql.append(" LIMIT $").append(pageParams.size() + 1);
        pageParams.add(size);
        pageSql.append(" OFFSET $").append(pageParams.size() + 1);
        pageParams.add(offset);

        queryConditionUtil.setPageStr(pageSql);
        queryConditionUtil.setPageParams(pageParams);

        StringBuilder countStr = new StringBuilder("SELECT COUNT(*) as count FROM  (").append(sql).append(") c ");
        queryConditionUtil.setQueryStr(countStr);
        return queryConditionUtil;
    }

    /**
     * 创建新用户
     */
    public Uni<SysUser> create(SysUser user) {

        List<String> insertColumns = Arrays.asList("created_at", "level", "in_code", "device_id", "channel", "nick_name");

        // 列名拼接
        String columns = String.join(", ", insertColumns);

        // 动态生成占位符（$1, $2, $3...）
        List<String> placeholderList = new ArrayList<>();
        for (int i = 0; i < insertColumns.size(); i++) {
            placeholderList.add("$" + (i + 1));
        }
        String placeholders = String.join(", ", placeholderList);

        String sql = String.format(
                "INSERT INTO wz_users (%s) VALUES (%s)  RETURNING id",
                columns,
                placeholders
        );
        user.setCreated_at(LocalDateTime.now());

        Tuple tuple = Tuple.from(Arrays.asList(user.getCreated_at(), user.getLevel(), null, user.getDevice_id(), user.getChannel(), user.getNick_name()));

        return client.preparedQuery(sql.toString())
                .execute(tuple)
                .onItem().transform(rows -> {
                    Long id = rows.iterator().next().getLong("id");
                    user.setId(id);
                    return user;
                });
    }

    /**
     * 更新用户登录时间
     */
//    public Uni<SysUser> updateLoginTime(String userId, Long loginTime) {
//        String sql = "UPDATE wz_users SET last_login_time = ?, updated_time = ? WHERE user_id = ?";
//        return client.preparedQuery(sql)
//                .execute(Tuple.of(loginTime, System.currentTimeMillis(), userId))
//                .onItem().transform(rows -> {
//                    SysUser user = new SysUser();
//                    user.setUserId(userId);
//                    user.setLastLoginTime(loginTime);
//                    return user;
//                });
//    }

    /**
     * 更新设备ID
     */
//    public Uni<Void> updateDeviceId(String userId, String deviceId) {
//        String sql = "UPDATE wz_users SET device_id = ?, updated_time = ? WHERE user_id = ?";
//        return client.preparedQuery(sql)
//                .execute(Tuple.of(deviceId, System.currentTimeMillis(), userId))
//                .onItem().transformToUni(rows -> Uni.createFrom().voidItem());
//    }

    /**
     * 批量更新用户登录信息（设备ID、登录时间、昵称、等级、组名）
     */
    public Uni<SysUser> updateUserLoginInfo(Long userId, String deviceId, Long loginTime,
                                            String nickName, Integer userLevel, String groupName) {
        String sql = "UPDATE wz_users SET device_id = ?, last_login_time = ?, nick_name = ?, " +
                "user_level = ?, group_name = ?, updated_time = ? WHERE user_id = ?";

        return client.preparedQuery(sql)
                .execute(Tuple.tuple(Arrays.asList(deviceId, loginTime, nickName, userLevel, groupName,
                        System.currentTimeMillis(), userId)))
                .onItem().transformToUni(rows -> {
                    // 返回更新后的用户信息
                    return findByUserId(userId);
                });
    }

    /**
     * 将数据库行映射为SysUser对象
     */
    private SysUser mapRowToSysUser(Row row) {
        SysUser user = new SysUser();
        user.setId(row.getLong("id"));
        user.setCreated_at(row.getLocalDateTime("created_at"));
        user.setNick_name(row.getString("nick_name"));
        user.setLevel(row.getInteger("level"));
        user.setChannel(row.getString("channel"));
        user.setDevice_id(row.getString("device_id"));
        return user;
    }

    /**
     * 将数据库行映射为SysUser对象
     */
    private WzUser mapRowToWzUser(Row row) {
        WzUser user = new WzUser();
        user.setId(row.getLong("id"));
        user.setCreated_at(row.getLocalDateTime("created_at"));
        user.setNick_name(row.getString("nick_name"));
        user.setLevel(row.getInteger("level"));
        user.setChannel(row.getString("channel"));
        user.setDevice_id(row.getString("device_id"));
        user.setIs_withdrawal(row.getBoolean("is_withdrawal"));
        user.setIs_main(row.getBoolean("is_main"));
        user.setChild_max_count(row.getLong("child_max_count"));
        user.setIs_enable(row.getBoolean("is_enable"));
        return user;
    }

    private UserBindRelation mapRowToUserBindRelation(Row row) {
        UserBindRelation user = new UserBindRelation();
        user.setUserid(row.getLong("user_id"));
        user.setCreatedAt(row.getLocalDateTime("created_at"));
        user.setMainUserId(row.getLong("main_user_id"));
        return user;
    }

    private BindRecordResponseDto mapRowToBindRelation(Row row) {
        BindRecordResponseDto user = new BindRecordResponseDto();
        user.setUserId(row.getLong("user_id"));
        user.setBindTime(row.getLocalDateTime("created_at"));
        return user;
    }
}